﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using WebTemplate.DAL.Entities;


namespace WebTemplate.DAL.Repositories
{
    public class CoursesRepository
    {
        public void Insert(Courses Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[Course_Insert]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Name", Item.Name);
            command.Parameters.AddWithValue("@Description", Item.Description);
            command.Parameters.AddWithValue("@PreReq", Item.PreReq);
            command.Parameters.AddWithValue("@Department", Item.Department);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public void Update(Courses Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[Course_Update]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID", Item.ID);
            command.Parameters.AddWithValue("@Name", Item.Name);
            command.Parameters.AddWithValue("@Description", Item.Description);
            command.Parameters.AddWithValue("@PreReq", Item.PreReq);
            command.Parameters.AddWithValue("@Department", Item.Department);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public void Delete(int ID)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[Course_Delete]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID", ID);
            connecton.Open();
            command.ExecuteNonQuery();
            connecton.Close();
        }

        public List<Courses> Select(CoursesSearchCriteria Item)
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[Course_Select]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@ID", Item.ID);
            command.Parameters.AddWithValue("@Name", Item.Name);
            command.Parameters.AddWithValue("@Description", Item.Description);
            command.Parameters.AddWithValue("@PreReq", Item.PreReq);
            command.Parameters.AddWithValue("@Department", Item.Department);
            connecton.Open();

            SqlDataReader reader = command.ExecuteReader();
            List<Courses> reg = new List<Courses>();

            while(reader.Read())
            {
                Courses obj = new Courses();

                obj.ID = reader.GetInt32(0);
                obj.Name = reader.GetString(1);
                obj.Description = reader.GetString(2);
                obj.PreReq = reader.GetString(3);
                obj.Department = reader.GetString(4);

                reg.Add(obj);
            }

           
            return reg;
        }

        public List<Courses> DropDownList()
        {
            SqlConnection connecton = new SqlConnection();
            connecton.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connecton;
            command.CommandText = "[DropDownListCourses]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            connecton.Open();

            SqlDataReader reader = command.ExecuteReader();
            List<Courses> reg = new List<Courses>();

            while (reader.Read())
            {
                Courses obj = new Courses();

                obj.ID = reader.GetInt32(0);
                obj.Name = reader.GetString(1);
                obj.Description = reader.GetString(2);
                obj.PreReq = reader.GetString(3);
                obj.Department = reader.GetString(4);

                reg.Add(obj);
            }

            connecton.Close();
            return reg;
        }
    }
}
